# -*- coding: utf-8 -*-
"""
乒乓球培训管理系统 - 学员控制器

学员管理相关的业务逻辑
包括学员CRUD、统计分析等功能
"""

from datetime import datetime, date, timedelta
from decimal import Decimal
from sqlalchemy import and_, or_, func, desc, asc
from flask_login import current_user

from app import db
from app.models.user import User
from app.models.campus import Campus
from app.models.relation import StudentCoachRelation
from app.models.reservation import Reservation
from app.models.account import UserAccount
from app.utils.file_upload import save_uploaded_file


class StudentController:
    """学员控制器"""

    @staticmethod
    def get_student_list(page=1, per_page=12, keyword=None, campus_id=None,
                        status=None, has_coach=None, sort_by='created_at_desc'):
        """
        获取学员列表

        Args:
            page (int): 页码
            per_page (int): 每页数量
            keyword (str): 搜索关键词
            campus_id (int): 校区ID
            status (str): 状态
            has_coach (str): 是否有教练
            sort_by (str): 排序方式

        Returns:
            tuple: (分页对象, 总数)
        """
        try:
            # 基础查询
            query = User.query.filter_by(role='student')

            # 权限过滤
            if current_user.is_campus_admin():
                query = query.filter_by(campus_id=current_user.campus_id)

            # 关键词搜索
            if keyword:
                keyword = f'%{keyword}%'
                query = query.filter(
                    or_(
                        User.real_name.like(keyword),
                        User.phone.like(keyword),
                        User.email.like(keyword),
                        User.username.like(keyword)
                    )
                )

            # 校区筛选
            if campus_id:
                query = query.filter_by(campus_id=campus_id)

            # 状态筛选
            if status:
                query = query.filter_by(status=status)

            # 是否有教练筛选
            if has_coach:
                if has_coach == 'yes':
                    # 有教练的学员
                    student_ids_with_coach = db.session.query(StudentCoachRelation.student_id).filter_by(
                        status='approved'
                    ).distinct()
                    query = query.filter(User.id.in_(student_ids_with_coach))
                elif has_coach == 'no':
                    # 没有教练的学员
                    student_ids_with_coach = db.session.query(StudentCoachRelation.student_id).filter_by(
                        status='approved'
                    ).distinct()
                    query = query.filter(~User.id.in_(student_ids_with_coach))

            # 排序
            if sort_by == 'created_at_desc':
                query = query.order_by(desc(User.created_at))
            elif sort_by == 'created_at_asc':
                query = query.order_by(asc(User.created_at))
            elif sort_by == 'name_asc':
                query = query.order_by(asc(User.real_name))
            elif sort_by == 'name_desc':
                query = query.order_by(desc(User.real_name))
            elif sort_by == 'last_login_desc':
                # 由于没有last_login_at字段，按创建时间倒序排序
                query = query.order_by(desc(User.created_at))
            elif sort_by == 'reservation_count_desc':
                # 按预约次数排序
                reservation_counts = db.session.query(
                    Reservation.student_id,
                    func.count(Reservation.id).label('reservation_count')
                ).group_by(Reservation.student_id).subquery()

                query = query.outerjoin(
                    reservation_counts,
                    User.id == reservation_counts.c.student_id
                ).order_by(desc(reservation_counts.c.reservation_count))

            # 分页
            pagination = query.paginate(
                page=page,
                per_page=per_page,
                error_out=False
            )

            total = query.count()

            return pagination, total

        except Exception as e:
            print(f"Error in get_student_list: {str(e)}")
            return None, 0

    @staticmethod
    def get_student_by_id(student_id):
        """
        根据ID获取学员

        Args:
            student_id (int): 学员ID

        Returns:
            User: 学员对象或None
        """
        try:
            student = User.query.filter_by(id=student_id, role='student').first()

            # 权限检查
            if student and current_user.is_campus_admin():
                if student.campus_id != current_user.campus_id:
                    return None

            return student

        except Exception as e:
            print(f"Error in get_student_by_id: {str(e)}")
            return None

    @staticmethod
    def update_student(student, form_data):
        """
        更新学员信息

        Args:
            student (User): 学员对象
            form_data (dict): 表单数据

        Returns:
            tuple: (成功标志, 错误信息)
        """
        try:
            # 权限检查
            if current_user.is_campus_admin() and student.campus_id != current_user.campus_id:
                return False, "无权限修改此学员信息"

            # 校区权限检查
            new_campus_id = form_data.get('campus_id')
            if current_user.is_campus_admin() and new_campus_id and new_campus_id != current_user.campus_id:
                return False, "您只能将学员分配到自己管理的校区"

            # 更新基本信息
            student.real_name = form_data.get('real_name', student.real_name)
            student.phone = form_data.get('phone', student.phone)
            student.email = form_data.get('email') or None
            student.gender = form_data.get('gender') or None
            student.age = form_data.get('age') or None
            if new_campus_id:
                student.campus_id = new_campus_id
            student.status = form_data.get('status', student.status)
            student.updated_at = datetime.now()

            # 处理头像上传
            photo_file = form_data.get('photo')
            if photo_file:
                photo_url = save_uploaded_file(photo_file, 'students')
                if photo_url:
                    student.photo_url = photo_url

            # 处理备注（如果有的话，可以扩展User模型添加notes字段）
            notes = form_data.get('notes')
            if hasattr(student, 'notes'):
                student.notes = notes

            db.session.commit()
            return True, None

        except Exception as e:
            db.session.rollback()
            return False, f"更新失败：{str(e)}"

    @staticmethod
    def delete_student(student_id):
        """
        删除学员（软删除）

        Args:
            student_id (int): 学员ID

        Returns:
            tuple: (成功标志, 错误信息)
        """
        try:
            student = User.query.filter_by(id=student_id, role='student').first()
            if not student:
                return False, "学员不存在"

            # 权限检查
            if current_user.is_campus_admin() and student.campus_id != current_user.campus_id:
                return False, "无权限删除此学员"

            # 检查是否有关联数据
            associations = StudentController.check_student_associations(student_id)
            if associations['has_future_reservations']:
                return False, "该学员还有未来的课程预约，无法删除"

            # 软删除：设置为inactive状态
            student.status = 'inactive'
            student.updated_at = datetime.now()

            db.session.commit()
            return True, None

        except Exception as e:
            db.session.rollback()
            return False, f"删除失败：{str(e)}"

    @staticmethod
    def check_student_associations(student_id):
        """
        检查学员关联数据

        Args:
            student_id (int): 学员ID

        Returns:
            dict: 关联数据统计
        """
        try:
            # 活跃教练关系数
            active_coaches = StudentCoachRelation.query.filter_by(
                student_id=student_id,
                status='approved'
            ).count()

            # 未来的预约数
            future_reservations = Reservation.query.filter(
                Reservation.student_id == student_id,
                Reservation.status.in_(['pending', 'confirmed']),
                Reservation.reservation_date >= date.today()
            ).count()

            # 总教练关系数
            total_coach_relations = StudentCoachRelation.query.filter_by(
                student_id=student_id
            ).count()

            # 总预约数
            total_reservations = Reservation.query.filter_by(
                student_id=student_id
            ).count()

            # 账户余额
            account = UserAccount.query.filter_by(user_id=student_id).first()
            account_balance = float(account.balance) if account else 0

            return {
                'has_active_coaches': active_coaches > 0,
                'has_future_reservations': future_reservations > 0,
                'has_account_balance': account_balance > 0,
                'active_coaches_count': active_coaches,
                'future_reservations_count': future_reservations,
                'total_coach_relations_count': total_coach_relations,
                'total_reservations_count': total_reservations,
                'account_balance': account_balance
            }

        except Exception as e:
            print(f"Error in check_student_associations: {str(e)}")
            return {
                'has_active_coaches': False,
                'has_future_reservations': False,
                'has_account_balance': False,
                'active_coaches_count': 0,
                'future_reservations_count': 0,
                'total_coach_relations_count': 0,
                'total_reservations_count': 0,
                'account_balance': 0
            }

    @staticmethod
    def get_student_statistics(student_id):
        """
        获取学员统计信息

        Args:
            student_id (int): 学员ID

        Returns:
            dict: 统计数据
        """
        try:
            today = date.today()
            week_start = today - timedelta(days=today.weekday())
            month_start = today.replace(day=1)

            # 教练统计
            total_coaches = StudentCoachRelation.query.filter_by(
                student_id=student_id,
                status='approved'
            ).count()

            pending_coach_applications = StudentCoachRelation.query.filter_by(
                student_id=student_id,
                status='pending'
            ).count()

            # 预约统计
            today_reservations = Reservation.query.filter(
                Reservation.student_id == student_id,
                func.date(Reservation.reservation_date) == today,
                Reservation.status.in_(['confirmed', 'completed'])
            ).count()

            week_reservations = Reservation.query.filter(
                Reservation.student_id == student_id,
                Reservation.reservation_date >= week_start,
                Reservation.status.in_(['confirmed', 'completed'])
            ).count()

            month_reservations = Reservation.query.filter(
                Reservation.student_id == student_id,
                Reservation.reservation_date >= month_start,
                Reservation.status.in_(['confirmed', 'completed'])
            ).count()

            total_reservations = Reservation.query.filter_by(
                student_id=student_id
            ).count()

            # 消费统计
            today_spending = db.session.query(func.sum(Reservation.price)).filter(
                Reservation.student_id == student_id,
                func.date(Reservation.reservation_date) == today,
                Reservation.status.in_(['completed'])
            ).scalar() or Decimal('0')

            week_spending = db.session.query(func.sum(Reservation.price)).filter(
                Reservation.student_id == student_id,
                Reservation.reservation_date >= week_start,
                Reservation.status.in_(['completed'])
            ).scalar() or Decimal('0')

            month_spending = db.session.query(func.sum(Reservation.price)).filter(
                Reservation.student_id == student_id,
                Reservation.reservation_date >= month_start,
                Reservation.status.in_(['completed'])
            ).scalar() or Decimal('0')

            total_spending = db.session.query(func.sum(Reservation.price)).filter(
                Reservation.student_id == student_id,
                Reservation.status.in_(['completed'])
            ).scalar() or Decimal('0')

            # 课时统计
            total_hours = db.session.query(func.sum(Reservation.duration_minutes)).filter(
                Reservation.student_id == student_id,
                Reservation.status == 'completed'
            ).scalar() or 0

            # 账户信息
            account = UserAccount.query.filter_by(user_id=student_id).first()
            account_balance = float(account.balance) if account else 0

            # 最后登录时间 (字段不存在，使用创建时间作为替代)
            student = User.query.get(student_id)
            last_login = student.created_at if student else None

            return {
                'total_coaches': total_coaches,
                'pending_coach_applications': pending_coach_applications,
                'today_reservations': today_reservations,
                'week_reservations': week_reservations,
                'month_reservations': month_reservations,
                'total_reservations': total_reservations,
                'today_spending': float(today_spending),
                'week_spending': float(week_spending),
                'month_spending': float(month_spending),
                'total_spending': float(total_spending),
                'total_hours': round(total_hours / 60, 1) if total_hours else 0,
                'account_balance': account_balance,
                'last_login': last_login
            }

        except Exception as e:
            print(f"Error in get_student_statistics: {str(e)}")
            return {
                'total_coaches': 0,
                'pending_coach_applications': 0,
                'today_reservations': 0,
                'week_reservations': 0,
                'month_reservations': 0,
                'total_reservations': 0,
                'today_spending': 0,
                'week_spending': 0,
                'month_spending': 0,
                'total_spending': 0,
                'total_hours': 0,
                'account_balance': 0,
                'last_login': None
            }

    @staticmethod
    def get_student_coaches(student_id):
        """
        获取学员的教练列表

        Args:
            student_id (int): 学员ID

        Returns:
            list: 教练关系列表
        """
        try:
            relations = StudentCoachRelation.query.filter_by(
                student_id=student_id
            ).order_by(desc(StudentCoachRelation.applied_at)).all()

            return relations

        except Exception as e:
            print(f"Error in get_student_coaches: {str(e)}")
            return []

    @staticmethod
    def get_student_recent_reservations(student_id, limit=10):
        """
        获取学员最近的预约记录

        Args:
            student_id (int): 学员ID
            limit (int): 限制数量

        Returns:
            list: 预约列表
        """
        try:
            reservations = Reservation.query.filter_by(
                student_id=student_id
            ).order_by(desc(Reservation.reservation_date), desc(Reservation.start_time)).limit(limit).all()

            return reservations

        except Exception as e:
            print(f"Error in get_student_recent_reservations: {str(e)}")
            return []

    @staticmethod
    def batch_operation(student_ids, action, **kwargs):
        """
        批量操作学员

        Args:
            student_ids (list): 学员ID列表
            action (str): 操作类型
            **kwargs: 其他参数

        Returns:
            tuple: (成功数量, 失败数量, 错误列表)
        """
        try:
            success_count = 0
            fail_count = 0
            errors = []

            for student_id in student_ids:
                try:
                    student = User.query.filter_by(id=student_id, role='student').first()
                    if not student:
                        errors.append(f"学员ID {student_id} 不存在")
                        fail_count += 1
                        continue

                    # 权限检查
                    if current_user.is_campus_admin() and student.campus_id != current_user.campus_id:
                        errors.append(f"无权限操作学员 {student.real_name}")
                        fail_count += 1
                        continue

                    if action == 'activate':
                        student.status = 'active'
                    elif action == 'deactivate':
                        student.status = 'inactive'
                    elif action == 'transfer_campus':
                        target_campus_id = kwargs.get('target_campus_id')
                        if target_campus_id:
                            # 权限检查
                            if current_user.is_campus_admin() and target_campus_id != current_user.campus_id:
                                errors.append(f"无权限将学员 {student.real_name} 转移到其他校区")
                                fail_count += 1
                                continue
                            student.campus_id = target_campus_id
                    elif action == 'delete':
                        associations = StudentController.check_student_associations(student_id)
                        if associations['has_future_reservations']:
                            errors.append(f"学员 {student.real_name} 还有未来预约，无法删除")
                            fail_count += 1
                            continue
                        student.status = 'inactive'

                    student.updated_at = datetime.now()
                    success_count += 1

                except Exception as e:
                    errors.append(f"操作学员ID {student_id} 时出错：{str(e)}")
                    fail_count += 1

            db.session.commit()
            return success_count, fail_count, errors

        except Exception as e:
            db.session.rollback()
            return 0, len(student_ids), [f"批量操作失败：{str(e)}"]

    @staticmethod
    def get_campus_student_summary(campus_id=None):
        """
        获取校区学员汇总统计

        Args:
            campus_id (int): 校区ID，None表示全部

        Returns:
            dict: 汇总统计
        """
        try:
            query = User.query.filter_by(role='student')

            # 权限过滤
            if current_user.is_campus_admin():
                query = query.filter_by(campus_id=current_user.campus_id)
            elif campus_id:
                query = query.filter_by(campus_id=campus_id)

            # 总数统计
            total = query.count()
            active = query.filter_by(status='active').count()
            inactive = query.filter_by(status='inactive').count()

            # 有教练的学员数
            student_ids_with_coach = db.session.query(StudentCoachRelation.student_id).filter_by(
                status='approved'
            ).distinct()

            with_coach = query.filter(User.id.in_(student_ids_with_coach)).count()
            without_coach = total - with_coach

            # 最近注册（7天内）
            recent_registered = query.filter(
                User.created_at >= datetime.now() - timedelta(days=7)
            ).count()

            return {
                'total': total,
                'active': active,
                'inactive': inactive,
                'with_coach': with_coach,
                'without_coach': without_coach,
                'recent_registered': recent_registered
            }

        except Exception as e:
            print(f"Error in get_campus_student_summary: {str(e)}")
            return {
                'total': 0,
                'active': 0,
                'inactive': 0,
                'with_coach': 0,
                'without_coach': 0,
                'recent_registered': 0
            }